package com.cennavi.mineplay.data;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import no.ecc.vectortile.VectorTileEncoder;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cennavi.mineplay.utils.ABUtils;
import com.cennavi.mineplay.utils.DBUtils;
import com.cennavi.mineplay.utils.GridHeatmapUtils;
import com.cennavi.mineplay.utils.HeatmapUtils;
import com.cennavi.mineplay.utils.HeatmapUtils.Grid;
import com.cennavi.mineplay.utils.HeatmapUtils.Grids;
import com.cennavi.mineplay.utils.JsonUtils;
import com.mercator.TileUtils;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.WKTReader;


@RestController
@RequestMapping(value = "/data")
public class DataService {
	
	private static Map<String,Object> cache = new HashMap<>();
	
	@Autowired
	protected JdbcTemplate jdbc;

	
	@RequestMapping(value = "/pbf/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] pbf(@PathVariable int x, @PathVariable int y,
			@PathVariable int z,String name,String condition) {
		
//		if ((condition ==null || condition.length()==0) && cache.containsKey(name+"-"+x+"-"+"-"+y+"-"+z)){
//			return (byte[])cache.get(name+"-"+x+"-"+"-"+y+"-"+z);
//		}
		
		String sql = "select *,st_astext(geom) as geom1 from "+name+" where st_intersects(geom,st_geomfromtext(?,4326))";

		if (condition != null && condition.length()>0)
			sql +=" and "+ JsonUtils.condition2String(condition);
		
		try {
			
			byte[] value = DBUtils.getProtoBuf(jdbc, sql, x, y, z);
			
//			if (condition == null){
//				cache.put(name+"-"+x+"-"+"-"+y+"-"+z, value);
//			}
			
			return value;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	@RequestMapping(value = "/pbf_particle/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] pbf_particle(@PathVariable int x, @PathVariable int y,
			@PathVariable int z,String name,int speed,int range) {
		
		String sql = "select *,st_astext(geom) as geom1 from "+name+" where st_intersects(geom,st_geomfromtext(?,4326))";

		try {
			
			return DBUtils.getParticleProtoBuf(jdbc, sql, x, y, z, speed,range);

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	
	
	@RequestMapping(value = "/pbf/km/grid", produces = "application/x-protobuf")
	public byte[] km_grid(String name,String condition,int km) {
		
		String sql = "select st_x(geom) as lng,st_y(geom) as lat from "+ name +" where 1=1 ";
		
		if (condition != null && condition.length()>0)
			sql +=" and "+ JsonUtils.condition2String(condition);
		
		try {
			byte[] value = GridHeatmapUtils.getGrids(jdbc, sql, km,0);
			
			return value;
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	@RequestMapping(value = "/pbf/km/hexgrid", produces = "application/x-protobuf")
	public byte[] km_hexgrid(String name,String condition,int km) {
		
		String sql = "select st_x(geom) as lng,st_y(geom) as lat from "+ name +" where 1=1 ";
		
		if (condition != null && condition.length()>0)
			sql +=" and "+ JsonUtils.condition2String(condition);
		
		try {
			byte[] value = GridHeatmapUtils.getGrids(jdbc, sql, km,1);
			
			return value;
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	@RequestMapping(value = "/pbf/grid/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] pbf_grid(@PathVariable int x, @PathVariable int y,
			@PathVariable int z,String name,String condition,Integer num) {
		if ( num ==null || num == 0) num = 50;
		
		if ((condition ==null || condition.length()==0) && cache.containsKey(name+"-"+x+"-"+"-"+y+"-"+z+"-"+num)){
			return (byte[])cache.get("grid"+name+"-"+x+"-"+"-"+y+"-"+z+"-"+num);
		}

		String sql = "select st_astext(geom) as wkt from "+name+" where st_intersects(geom,st_geomfromtext(?,4326))";

		if (condition != null && condition.length()>0)
			sql +=" and "+ JsonUtils.condition2String(condition);
		try {
			
			Map<String,Object> boundMap = TileUtils.parseXyz2BoundObject(x, y, z);
			
			String wkt = boundMap.get("wkt").toString();
			
			double[] tileRange = (double[])boundMap.get("bound");
			
			double min_lng = tileRange[0];
			double max_lng = tileRange[1];
			double min_lat = tileRange[2];
			double max_lat = tileRange[3];
			
			double[] range = HeatmapUtils.getGeomRange(name, jdbc);
			
			if (((max_lng - min_lng) / 2 >= (range[1] - range[0])) || ((max_lat - min_lat) /2 >= (range[3]- range[2]))){
				return null;
			}
			
			Grids grids = HeatmapUtils.getGrids(range, min_lng, max_lng, min_lat, max_lat, num);
			
			List<List<Grid>> listGrid = grids.getList();
			
			List<Map<String, Object>> results = jdbc.queryForList(sql, wkt);
			
			for(Map<String,Object> m: results){
				String pointWkt = m.get("wkt").toString().toLowerCase().replace("point(", "").replace(")", "");
				
				String[] splits = pointWkt.split(" ");
				
				double lng = Double.parseDouble(splits[0]);
				double lat = Double.parseDouble(splits[1]);
				
				Grid grid = grids.getGrid(lng, lat);
				
				
				grid.setCount(1);
			}
			
			VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);
			
			
			for(List<Grid> rowGrid : listGrid){
				
				for(Grid grid:rowGrid){
					if (grid.getCount()>0){
						
						Geometry geom = new WKTReader().read(grid.getGridWKT());

						TileUtils.convert2Piexl(x, y, z, geom);
						
						Map<String,Object> m = new HashMap<>();
						
						m.put("count", grid.getCount());
						
						
						
						vte.addFeature("position", m, geom);
					}
				}
			}
			
			
			byte[] value = vte.encode();
			
			if (condition ==null || condition.length() == 0){
				cache.put("grid"+name+"-"+x+"-"+"-"+y+"-"+z+"-"+num,value);
			}
			
			return value;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	
	
	@RequestMapping(value = "/pbf/grid_num/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] pbf_gridNum(@PathVariable int x, @PathVariable int y,
			@PathVariable int z,String name,String condition,Integer num) {
		
		if ( num ==null || num == 0) num = 50;
		
		if ((condition ==null || condition.length()==0) && cache.containsKey(name+"-"+x+"-"+"-"+y+"-"+z+"-"+num)){
			return (byte[])cache.get("grid_num"+name+"-"+x+"-"+"-"+y+"-"+z+"-"+num);
		}
		
		String sql = "select st_astext(geom) as wkt from "+name+" where st_intersects(geom,st_geomfromtext(?,4326))";

		if (condition != null && condition.length()>0)
			sql +=" and "+ JsonUtils.condition2String(condition);
		
		try {
			
			Map<String,Object> boundMap = TileUtils.parseXyz2BoundObject(x, y, z);
			
			String wkt = boundMap.get("wkt").toString();
			
			double[] tileRange = (double[])boundMap.get("bound");
			
			double min_lng = tileRange[0];
			double max_lng = tileRange[1];
			double min_lat = tileRange[2];
			double max_lat = tileRange[3];
			
			
			
			double[] range = HeatmapUtils.getGeomRange(name, jdbc);
			
			if (((max_lng - min_lng) / 2 >= (range[1] - range[0])) || ((max_lat - min_lat) /2 >= (range[3]- range[2]))){
				return null;
			}
			
			Grids grids = HeatmapUtils.getGrids(range, min_lng, max_lng, min_lat, max_lat, num);
			
			List<List<Grid>> listGrid = grids.getList();
			
			List<Map<String, Object>> results = jdbc.queryForList(sql, wkt);
			
			for(Map<String,Object> m: results){
				String pointWkt = m.get("wkt").toString().toLowerCase().replace("point(", "").replace(")", "");
				
				String[] splits = pointWkt.split(" ");
				
				double lng = Double.parseDouble(splits[0]);
				double lat = Double.parseDouble(splits[1]);
				
				Grid grid = grids.getGrid(lng, lat);
				
				
				grid.setCount(1);
			}
			
			VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);
			
			
			for(List<Grid> rowGrid : listGrid){
				
				for(Grid grid:rowGrid){
					if (grid.getCount()>0){
						
						Geometry geom = new WKTReader().read(grid.getGridWKT());
						
						
						
						Geometry center = geom.getCentroid();
						

						TileUtils.convert2Piexl(x, y, z, geom);
						TileUtils.convert2Piexl(x, y, z, center);
						
						Map<String,Object> m = new HashMap<>();
						
						m.put("count", grid.getCount());
						
						
						
						vte.addFeature("position", m, geom);
						vte.addFeature("grid-num", m, center);
					}
				}
			}
			
			
			byte[] value = vte.encode();
			
			if (condition ==null || condition.length() == 0){
				cache.put("grid_num"+name+"-"+x+"-"+"-"+y+"-"+z+"-"+num,value);
			}
			
			return value;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	@RequestMapping(value = "/pbf/hexgrid/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] pbf_hexgrid(@PathVariable int x, @PathVariable int y,
			@PathVariable int z,String name,String condition,Integer num) {
		
		if ( num ==null || num == 0) num = 50;
		
		if ((condition ==null || condition.length()==0) && cache.containsKey(name+"-"+x+"-"+"-"+y+"-"+z+"-"+num)){
			return (byte[])cache.get("hexgrid"+name+"-"+x+"-"+"-"+y+"-"+z+"-"+num);
		}
		
		String sql = "select st_astext(geom) as wkt from "+name+" where st_intersects(geom,st_geomfromtext(?,4326))";

		if (condition != null && condition.length()>0)
			sql +=" and "+ JsonUtils.condition2String(condition);
		try {
			
			Map<String,Object> boundMap = TileUtils.parseXyz2BoundObject(x, y, z);
			
			String wkt = boundMap.get("wkt").toString();
			
			double[] tileRange = (double[])boundMap.get("bound");
			
			double min_lng = tileRange[0];
			double max_lng = tileRange[1];
			double min_lat = tileRange[2];
			double max_lat = tileRange[3];
			
			double[] range = HeatmapUtils.getGeomRange(name, jdbc);
			
			if (((max_lng - min_lng) / 2 >= (range[1] - range[0])) || ((max_lat - min_lat) /2 >= (range[3]- range[2]))){
				return null;
			}
			
			Grids grids = HeatmapUtils.getGrids(range, min_lng, max_lng, min_lat, max_lat, num);
			
			List<List<Grid>> listGrid = grids.getList();
			
			List<Map<String, Object>> results = jdbc.queryForList(sql, wkt);
			
			for(Map<String,Object> m: results){
				String pointWkt = m.get("wkt").toString().toLowerCase().replace("point(", "").replace(")", "");
				
				String[] splits = pointWkt.split(" ");
				
				double lng = Double.parseDouble(splits[0]);
				double lat = Double.parseDouble(splits[1]);
				
				Grid grid = grids.getGrid(lng, lat);
				
				grid.setCount(1);
			}
			
			VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);
			
			
			for(List<Grid> rowGrid : listGrid){
				
				for(Grid grid:rowGrid){
					if (grid.getCount()>0){
						
						Geometry geom = new WKTReader().read(grid.getHexgridWKT());

						TileUtils.convert2Piexl(x, y, z, geom);
						
						Map<String,Object> m = new HashMap<>();
						
						m.put("count", grid.getCount() );
						
						
						vte.addFeature("position", m, geom);
					}
				}
			}
			
			
			byte[] value = vte.encode();
			
			if (condition ==null || condition.length() == 0){
				cache.put("hexgrid"+name+"-"+x+"-"+"-"+y+"-"+z+"-"+num,value);
			}
			
			return value;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	
	@RequestMapping(value = "/geojson")
	public Object geojson(String name,String condition) {
		
//		if (cache.containsKey(name) && condition==null)
//			return cache.get(name);
		
		
		String sql = "select *,st_asgeojson(geom) as geojson from "+name;
		
		if (condition != null && condition.length()>0)
			sql +=" where "+ JsonUtils.condition2String(condition);

		try {
			Object value = DBUtils.getGeojson(jdbc, sql);
			
//			if (condition == null)
//				cache.put(name, value);
			
			return value;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
		
	}
	
	
	@RequestMapping(value = "/chart")
	public Object chart(String param) {

		try {
			JSONObject json = JSON.parseObject(param);

			String tableName = json.getString("table_name");

			String duliang = json.getString("dimension");
			
			JSONArray jaAnalyze = json.getJSONArray("analyze");

			StringBuilder sb = new StringBuilder("select ");

			sb.append(duliang);
			
			sb.append(" as name");
			
			for(int i=0;i<jaAnalyze.size();i++){
				JSONObject kv = jaAnalyze.getJSONObject(i);
				
				String fenxi = kv.getString("attr_fun");
				
				String fenxiAttr = kv.getString("attr");
				
				sb.append(",");
				sb.append(fenxi);
				
				sb.append("(");
				sb.append(fenxiAttr);
				sb.append(") as ");
				
				sb.append(fenxiAttr+"_"+fenxi);
			}
			

			sb.append(" from ");

			sb.append(tableName);

			sb.append(" group by ");
			sb.append(duliang);
			sb.append(" order by "+duliang);

			String sql = sb.toString();
			
			return jdbc.queryForList(sql);
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return null;
	}
	
	

	@RequestMapping(value = "/ab/point", produces = "application/x-protobuf")
	public byte[] abPoint(String name,String attr,String condition) {
		
//		if ((condition ==null || condition.length()==0) && cache.containsKey(name+"-"+attr)){
//			return (byte[])cache.get(name+"-"+attr);
//		}
		
		String sql = "select "+attr+",st_astext(geom) as geom from "+name;
		
		if (condition != null && condition.length()>0)
			sql +=" where "+ JsonUtils.condition2String(condition);

		try {
			
//			return ABUtils.getABPoint(jdbc, sql);
			
			byte[] value = ABUtils.getABPoint(jdbc, sql);
			
//			if (condition ==null || condition.length() == 0){
//				cache.put(name+"-"+attr,value);
//			}
			
			return value;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	@RequestMapping(value = "/ab/od", produces = "application/x-protobuf")
	public byte[] abOd(String name,String attr,String condition) {
		
		String sql = "select st_astext(geom) as geom from "+name;
		
//		if ((condition ==null || condition.length()==0) && cache.containsKey(name+"-"+attr)){
//			return (byte[])cache.get(name+"-"+attr);
//		}
		
		if (condition != null && condition.length()>0)
			sql +=" where "+ JsonUtils.condition2String(condition);

		try {
			
//			return ABUtils.getABOd(jdbc, sql);
			
			byte[] value = ABUtils.getABOd(jdbc, sql);
			
//			if (condition ==null || condition.length() == 0){
//				cache.put(name+"-"+attr,value);
//			}
			
			return value;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	
	
	@RequestMapping(value = "/ab/od_arc", produces = "application/x-protobuf")
	public byte[] abOdAndArc(String name,String attr,String condition) {
		
		String sql = "select "+attr+",st_astext(geom) as geom from "+name ;
		
//		if ((condition ==null || condition.length()==0) && cache.containsKey(name+"-"+attr)){
//			return (byte[])cache.get(name+"-"+attr);
//		}
		
		if (condition != null && condition.length()>0)
			sql +=" where "+ JsonUtils.condition2String(condition);
		
		 
			sql += " limit 1500";
		try {
			
//			return ABUtils.getABOd(jdbc, sql);
			
			byte[] value = ABUtils.getABOdWithAttr(jdbc, sql, attr);
			
//			if (condition ==null || condition.length() == 0){
//				cache.put(name+"-"+attr,value);
//			}
			
			return value;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	
	
	
	@RequestMapping(value = "/ab/gps", produces = "application/x-protobuf")
	public byte[] abGps(String name,String attr,String condition) {
		
//		if ((condition ==null || condition.length()==0) && cache.containsKey(name+"-"+attr)){
//			return (byte[])cache.get(name+"-"+attr);
//		}
		
		String sql = "select st_astext(geom) as geom from "+name;

		if (condition != null && condition.length()>0)
			sql +=" where "+ JsonUtils.condition2String(condition);
		
		try {
			
//			return ABUtils.getABLink(jdbc, sql);
			
			byte[] value = ABUtils.getABLink(jdbc, sql);
			
//			if (condition ==null || condition.length() == 0){
//				cache.put(name+"-"+attr,value);
//			}
			
			return value;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}
	
	
	
	
	@RequestMapping(value = "/data_multi")
	public Object dataSql(String param) {

		try {
			JSONObject json = JSON.parseObject(param);

			String sql = json.getString("sql").toLowerCase().trim();
			
			if (sql.indexOf("drop")>=0 || sql.indexOf("delete")>=0){
				Map<String,Object> result = new HashMap<String,Object>();
				result.put("error_info", "非法操作数据(DROP,DELETE)");
				return result;
			}
			
			Map<String,Object> map = new HashMap<String,Object>();
			
			if (sql.indexOf("update") >=0){
				
				
				int pidx_s = sql.indexOf("update") + 6;
				
				boolean flag = true;
				
				while(flag){
					if (sql.charAt(pidx_s) == ' '){
						pidx_s++;
					}else{
						flag = false;
					}
				}
				
				flag = true;
				
				int pidx_e = pidx_s;
				
				while(flag){
					if (sql.charAt(pidx_e) != ' '){
						pidx_e++;
					}else{
						flag = false;
					}
				}
				
				String tableName = sql.substring(pidx_s, pidx_e);
				
				jdbc.update(sql);
				
				map.put("operation","update");
				map.put("table_name", tableName);
			}else{
				map.put("operation", "select");
				map.put("content", jdbc.queryForList("select * from ("+sql+") as foo limit 1000"));
			}
			
			return map;
			
		}catch(Exception e){
			e.printStackTrace();
			Map<String,Object> result = new HashMap<String,Object>();
			result.put("error_info", e.getMessage());
			return result;
		}
		
		 
	}
	
	
	
}
